from DataAccess.DBConnFactory import DBConnFactory
from Misc.Utils import *

from string import Template
from datetime import timedelta

def query_pnl(start_date, end_date, portf_list=None, tick_list=None):

	sql_tpl = Template('''select pre_rank.* from
      (select pnl.sec_ticker as sec_ticker,
             si."name" as sec_name,
             pnl.security_type as sec_type,
             pnl.ls as ls,
             pnl.lcc as pl_local,
             pnl.bcc as pl_base,
             pnl.bcc/(NVL(pv.mkt_val_post,0)+NVL(sr.amt,0)) as attribution,
             pnl.curncy as local_curncy,
             pi.currency as base_curncy,
             pnl.portf_id as portfolio_id,
             pi."name" as portf_name
      from
      (select sec_ticker,portf_id,security_type,curncy,ls,sum(local_cash_chg) as lcc, sum(base_cash_chg) as bcc from 
      (select trd.lticker as sec_ticker,
           trd.portfolio_id as portf_id,
           trd.security_type as security_type,
           trd.currency as curncy,
           decode(trd.trade_type,'BUY','LONG','SELL','LONG','SHORT') as ls,
           decode(trd.security_type, 'FUTURES', si."value", 1.0) *
           decode(trd.trade_type, 'BUY', -1, 'SHORT COVERING', -1, 1) *
           trd.amount * trd.cost_price  as local_cash_chg,
           decode(trd.security_type, 'FUTURES', si."value", 1.0) *
           decode(trd.trade_type, 'BUY', -1, 'SHORT COVERING', -1, 1) *
           trd.amount * trd.cost_price / fx.price as base_cash_chg  
        from transaction_tick_ex trd
        left join comm_misc_static_info si on si.ticker = trd.ticker
                        and si.field = 'FUT_VAL_PT'
		left join portfolio_info pi on pi.portfolio_id = trd.portfolio_id
		left join comm_fx_latest fx on fx.ref_date = TO_DATE('${END}', 'yyyy-mm-dd')
										   and fx.dom_curncy = trd.currency
										   and fx.for_curncy = pi.currency
       where trd.trade_date > TO_DATE('${START}', 'yyyy-mm-dd')
			and trd.trade_date <= TO_DATE('${END}', 'yyyy-mm-dd')
			and trd.security_type <> 'REPO' 
      union all
	  select decode(cd.security_type,'PNOTE',pni.underlying_ticker,cd.ticker) as sec_ticker,
			cd.portfolio_id as portf_id,
			cd.security_type as security_type,
			cd.currency as curncy,
			decode(sign(cd.amount), 1, 'LONG', 'SHORT') as ls,
			cd.amount as local_cash_chg,
			cd.amount / fx.price as base_cash_chg
	  from cash_dividend_flow cd
		left join pnote_info pni on pni.isin_code = cd.ticker
	  	left join portfolio_info pi on pi.portfolio_id = cd.portfolio_id
		left join comm_fx_latest fx on fx.ref_date = TO_DATE('${END}', 'yyyy-mm-dd')
										   and fx.dom_curncy = cd.currency
										   and fx.for_curncy = pi.currency
	  where event_date > TO_DATE('${START}', 'yyyy-mm-dd')
			and event_date <= TO_DATE('${END}', 'yyyy-mm-dd')
	  union all
      select me.lticker as sec_ticker,
           me.portfolio_id as portf_id,
           me.security_type as security_type,
           me.price_currency as curncy,
           decode(sign(me.amount), 1, 'LONG', 'SHORT') as ls,
           -1*me.amount*me.mkt_price*me.point_value as local_cash_chg,
           -1*me.amount*me.mkt_price*me.point_value / fx.price as base_cash_chg		   
      from mkt_expo me
	    left join portfolio_info pi on pi.portfolio_id = me.portfolio_id
		left join comm_fx_latest fx on fx.ref_date = TO_DATE('${START}', 'yyyy-mm-dd')
										   and fx.dom_curncy = me.price_currency
										   and fx.for_curncy = pi.currency	  
      where me.ref_date = TO_DATE('${START}', 'yyyy-mm-dd')
			and me.security_type <> 'REPO'
			and me.security_type <> 'CASH'
      union all
      select me.lticker as sec_ticker,
           me.portfolio_id as portf_id,
           me.security_type as security_type,
           me.price_currency as curncy,
           decode(sign(me.amount), 1, 'LONG', 'SHORT') as ls,
           me.amount*me.mkt_price*me.point_value as local_cash_chg,
           me.amount*me.mkt_price*me.point_value / fx.price as base_cash_chg		   
      from mkt_expo me
	    left join portfolio_info pi on pi.portfolio_id = me.portfolio_id
		left join comm_fx_latest fx on fx.ref_date = TO_DATE('${END}', 'yyyy-mm-dd')
										   and fx.dom_curncy = me.price_currency
										   and fx.for_curncy = pi.currency
      where me.ref_date = TO_DATE('${END}', 'yyyy-mm-dd')
			and me.security_type <> 'REPO'
			and me.security_type <> 'CASH')
      where ${PORTF_CRITERIA}
        and ${TICK_CRITERIA}
      group by sec_ticker,portf_id,security_type,curncy,ls) pnl
			left join comm_security_static_info si on si.ticker = pnl.sec_ticker
			left join portfolio_info pi on pi.portfolio_id = pnl.portf_id
			left join portfolio_mkt_val pv on pv.ref_date = TO_DATE('${START}', 'yyyy-mm-dd')
													   and pv.portfolio_id =pnl.portf_id
			left join 
				(select parent,sum(decode(type,'subscription',1,-1)*amount) amt 
				from amount_chg_ex@ACCT_DB
				where ref_date>TO_DATE('${START}', 'yyyy-mm-dd')
					and ref_date<TO_DATE('${END}', 'yyyy-mm-dd')
				group by parent) sr
				on sr.parent=pnl.portf_id) pre_rank
      left join security_type_rank r on r.sec_type=pre_rank.sec_type
      order by pre_rank.portfolio_id,r.rank,pre_rank.pl_base desc''')
	
	one_day = timedelta(days=1)	
	cut_of_date = start_date - one_day
	sql_text = sql_tpl.substitute(START=cut_of_date.isoformat(),
							END=end_date.isoformat(),
							TICK_CRITERIA=format_sql_criteria('sec_ticker', tick_list), 
							PORTF_CRITERIA=format_sql_criteria('portf_id', portf_list))

	cursor = DBConnFactory().get_db_connection('PKEDB').cursor()							
	cursor.execute(sql_text)
	return cursor.fetchall()
	